﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Configuration.Provider;
using System.Data;
using System.Data.Common;
using System.Net;
using System.Web.Hosting;

namespace Altairis.Sewen.PageStore {
    public class TablePageStoreProvider : PageStoreProvider {
        private const string DEFAULT_TABLE_NAME = "Pages";

        private ConnectionStringSettings connectionString;

        // Initialization and configuration

        public override void Initialize(string name, NameValueCollection config) {
            // Base init
            base.Initialize(name, config);

            // Read connection string
            this.ConnectionStringName = config.GetConfigValue("connectionStringName", null);
            if (string.IsNullOrWhiteSpace(this.ConnectionStringName)) throw new ConfigurationErrorsException("Required \"connectionStringName\" attribute not specified.");
            this.connectionString = ConfigurationManager.ConnectionStrings[this.ConnectionStringName];
            if (this.connectionString == null) throw new ConfigurationErrorsException(string.Format("Connection string \"{0}\" was not found.", this.ConnectionStringName));
            if (string.IsNullOrEmpty(this.connectionString.ProviderName)) throw new ConfigurationErrorsException(string.Format("Connection string \"{0}\" does not have specified the \"providerName\" attribute.", this.ConnectionStringName));

            // Read other configuration
            this.TableName = config.GetConfigValue("tableName", DEFAULT_TABLE_NAME);
            this.VersionTimeKind = config.GetConfigValue("versionTimeKind", DateTimeKind.Local);
            if (this.VersionTimeKind == DateTimeKind.Unspecified) throw new ConfigurationErrorsException("The \"versionTimeKind\" attribute cannot be set to \"Unspecified\".");

            // Throw error on excess attributes
            if (config.Count != 0) throw new ConfigurationErrorsException("Unrecognized configuration attributes found: " + string.Join(", ", config.AllKeys));
        }

        public string ConnectionStringName { get; private set; }

        public string TableName { get; private set; }

        public DateTimeKind VersionTimeKind { get; private set; }

        // Provider implementation

        public override void SavePage(SewenPage page) {
            // Validate arguments
            if (page == null) throw new ArgumentNullException("page");
            if (string.IsNullOrWhiteSpace(page.Title)) throw new ArgumentException("Page title cannot be empty or whitespace only string.", "page");
            if (string.IsNullOrWhiteSpace(page.Body)) throw new ArgumentException("Page body cannot be empty or whitespace only string.", "page");

            // Check if there is change against last known version
            var lastKnownPage = LoadPage(page.Slug);
            if (lastKnownPage != null && lastKnownPage.Body.Equals(page.Body)) return; // no changes made

            // Set parameters
            page.Version = DateTime.Now;
            page.SetContextInfo();

            // Insert new page version to database
            try {
                using (HostingEnvironment.Impersonate())
                using (var db = this.connectionString.CreateDbConnection())
                using (var cmd = this.CreateDbCommand("INSERT INTO $Pages (Slug, Version, UserName, IpAddress, Title, Body, Comment) VALUES (@Slug, @Version, @UserName, @IpAddress, @Title, @Body, @Comment)", db)) {
                    // Prepare command
                    cmd.AddParameterWithValue("@Slug", page.Slug);
                    cmd.AddParameterWithValue("@Version", this.GetVersionTime(page.Version));
                    cmd.AddParameterWithValue("@UserName", page.UserName);
                    cmd.AddParameterWithValue("@IPAddress", page.IpAddress.ToString());
                    cmd.AddParameterWithValue("@Title", page.Title);
                    cmd.AddParameterWithValue("@Body", page.Body);
                    cmd.AddParameterWithValue("@Comment", page.Comment);

                    // Execute command
                    db.Open();
                    cmd.ExecuteNonQuery();
                    db.Close();
                }
            }
            catch (Exception ex) {
                throw new ProviderException("Error while performing database operation", ex);
            }
        }

        public override SewenPage LoadPage(string name, DateTime version) {
            // Validate arguments
            if (string.IsNullOrWhiteSpace(name)) return null;

            // Find page
            try {
                using (HostingEnvironment.Impersonate())
                using (var db = this.connectionString.CreateDbConnection())
                using (var cmd = this.CreateDbCommand("SELECT Version, UserName, IpAddress, Title, Body, Comment FROM $Pages WHERE Slug=@Slug AND Version<=@Version ORDER BY Version DESC", db)) {
                    // Prepare command
                    cmd.AddParameterWithValue("@Slug", SewenPage.GetSlug(name));
                    cmd.AddParameterWithValue("@Version", this.GetVersionTime(version));

                    // Execute command
                    db.Open();
                    using (var r = cmd.ExecuteReader(CommandBehavior.SingleRow)) {
                        if (!r.Read()) return null;
                        return GetPageFromReader(r);
                    }
                }
            }
            catch (Exception ex) {
                throw new ProviderException("Error while performing database operation", ex);
            }
        }

        public override IEnumerable<SewenPage> LoadPageVersions(string name) {
            // Validate arguments
            if (name == null) throw new ArgumentNullException("name");
            if (string.IsNullOrWhiteSpace(name)) throw new ArgumentException("Value cannot be empty or whitespace only string.", "name");

            // Find page
            try {
                using (HostingEnvironment.Impersonate())
                using (var db = this.connectionString.CreateDbConnection())
                using (var cmd = this.CreateDbCommand("SELECT Version, UserName, IpAddress, Title, Body, Comment FROM $Pages WHERE Slug=@Slug ORDER BY Version DESC", db)) {
                    // Prepare command
                    cmd.AddParameterWithValue("@Slug", SewenPage.GetSlug(name));

                    // Execute command
                    db.Open();
                    using (var r = cmd.ExecuteReader(CommandBehavior.SingleResult)) {
                        return new List<SewenPage>(GetPagesFromReader(r));
                    }
                }
            }
            catch (Exception ex) {
                throw new ProviderException("Error while performing database operation", ex);
            }
        }

        public override IEnumerable<SewenPage> FindPagesByTitle(string searchString, StringSearchMode mode) {
            // Prepare LIKE clause
            switch (mode) {
                case StringSearchMode.StartsWith:
                    searchString += "%";
                    break;
                case StringSearchMode.EndsWith:
                    searchString = "%" + searchString;
                    break;
                case StringSearchMode.Contains:
                    searchString = "%" + searchString + "%";
                    break;
                case StringSearchMode.Equals:
                default:
                    break;
            }

            // Find page
            try {
                using (HostingEnvironment.Impersonate())
                using (var db = this.connectionString.CreateDbConnection())
                using (var cmd = this.CreateDbCommand("SELECT Title, Version, UserName, IpAddress, Body, Comment FROM $Pages INNER JOIN (SELECT Slug, MAX(Version) AS LatestVersion FROM $Pages WHERE Title LIKE @Search GROUP BY Slug) AS P ON $Pages.Slug = P.Slug AND $Pages.Version = P.LatestVersion ORDER BY Title", db)) {
                    // Prepare command
                    cmd.AddParameterWithValue("@Search", searchString);

                    // Execute command
                    db.Open();
                    using (var r = cmd.ExecuteReader(CommandBehavior.SingleResult)) {
                        return new List<SewenPage>(GetPagesFromReader(r));
                    }
                }
            }
            catch (Exception ex) {
                throw new ProviderException("Error while performing database operation", ex);
            }
        }

        public override bool DeletePage(string name) {
            // Validate arguments
            if (name == null) throw new ArgumentNullException("name");
            if (string.IsNullOrWhiteSpace(name)) throw new ArgumentException("Value cannot be empty or whitespace only string.", "name");

            // Delete all versions of page
            try {
                using (HostingEnvironment.Impersonate())
                using (var db = this.connectionString.CreateDbConnection())
                using (var cmd = this.CreateDbCommand("DELETE FROM $Pages WHERE Slug=@Slug", db)) {
                    // Prepare command
                    cmd.AddParameterWithValue("@Slug", SewenPage.GetSlug(name));
                    db.Open();
                    return cmd.ExecuteNonQuery() != 0;
                }
            }
            catch (Exception ex) {
                throw new ProviderException("Error while performing database operation", ex);
            }
        }

        public override bool DeletePage(string name, DateTime version) {
            // Validate arguments
            if (name == null) throw new ArgumentNullException("name");
            if (string.IsNullOrWhiteSpace(name)) throw new ArgumentException("Value cannot be empty or whitespace only string.", "name");

            // Delete particular version of page
            try {
                using (HostingEnvironment.Impersonate())
                using (var db = this.connectionString.CreateDbConnection())
                using (var cmd = this.CreateDbCommand("DELETE FROM $Pages WHERE Slug=@Slug AND Version=@Version", db)) {
                    cmd.AddParameterWithValue("@Slug", SewenPage.GetSlug(name));
                    cmd.AddParameterWithValue("@Version", version);
                    db.Open();
                    return cmd.ExecuteNonQuery() != 0;
                }
            }
            catch (Exception ex) {
                throw new ProviderException("Error while performing database operation", ex);
            }
        }

        // Helper methods

        private static IEnumerable<SewenPage> GetPagesFromReader(DbDataReader r) {
            while (r.Read()) {
                yield return GetPageFromReader(r);
            }

        }

        private static SewenPage GetPageFromReader(DbDataReader r) {
            return new SewenPage {
                Body = r["Body"] as string,
                Comment = r["Comment"] as string,
                IpAddress = r["IpAddress"] as string,
                Title = r["Title"] as string,
                UserName = r["UserName"] as string,
                Version = (DateTime)r["Version"],
            };
        }

        private DateTime GetVersionTime(DateTime dt) {
            if (this.VersionTimeKind == DateTimeKind.Local) return dt.ToLocalTime();
            return dt.ToUniversalTime();
        }

        private DbCommand CreateDbCommand(string commandText, DbConnection db) {
            var cmd = db.CreateCommand();
            cmd.CommandText = commandText.Replace("$Pages", this.TableName);
            return cmd;
        }

    }
}
